package bbs.mvc.Beans;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DBUtils {
//	private static String DB_DRIVER = "com.mysql.cj.jdbc.Driver";
//	private static String DB_URL    = "jdbc:mysql://localhost:3306/bbs";
//	private static String DB_USER   = "root";
//	private static String DB_PASS   = "123456";
	
	public static void insert(String title,String author,String content,String picpath) throws SQLException {
		String sql="INSERT INTO blogdata (`title`, `author`, `content`, `picpath`) VALUES ('"+title+"', '"+author+"', '"+content+"' , '"+picpath+"')";
		excute(sql);
	}
	
	public static void deleteblog(String id) throws SQLException {
		String sql="DELETE FROM blogdata WHERE id ='"+id+"'";
		System.out.println(sql);
		excute(sql);
	}

	public static void signup(String username,String password)
	{
		String sql="INSERT INTO userdata(username,password)" +
				"VALUES('"
				+username
				+"','"
				+password
				+ "');";
		try {
			excute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void update(String title,String content,String picpath,int id) throws SQLException {
		String sql="UPDATE blogdata SET title=" + "'"+title +"'"+
				", content=" +"'"+content +"'"+
				",picpath=" +"'"+ picpath+"'"+
				" WHERE id =" +"'"+ id +"'"+
				";";
		excute(sql);
	}
		
	private static void excute(String sql) throws SQLException {
		PreparedStatement pstmt = null;
		Connection conn =null;
		try{
		Properties pro = new Properties();
		pro.load(DBUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
		DataSource ds = DruidDataSourceFactory.createDataSource(pro);
		conn =ds.getConnection();
		pstmt = conn.prepareStatement(sql);
		pstmt.execute();
		}
		catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//conn归还连接池
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
//		try {
//			Class.forName(DB_DRIVER);
//		} catch (ClassNotFoundException e) {
//			e.printStackTrace();
//		}
		
//		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
//			try (Statement statement = connection.createStatement()) {
//				statement.execute(sql);
//			}
//		}
	}
		/*public class DruidDemo1 {
    public static void main(String[] args) {
        String sql = "select * from student where id = ?";
        Scanner sc = new Scanner(System.in);
        int id = sc.nextInt();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //创建Properties对象，用于加载配置文件
            Properties pro = new Properties();
            //加载配置文件
            pro.load(DruidDemo1.class.getClassLoader().getResourceAsStream("druid.properties"));
            //获取数据库连接池对象
            DataSource ds = DruidDataSourceFactory.createDataSource(pro);
            //获取数据库连接对象
            conn = ds.getConnection();
            //获取statement,使用prepareStatement，防止sql注入
            pstmt = conn.prepareStatement(sql);
            //注入sql参数（sql中？将被替换）
            pstmt.setInt(1,id);
            //执行sql，返回数据集
            ResultSet rs = pstmt.executeQuery();
            //数据处理
            while(rs.next()){
                int id1 = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println(id+" "+ name);
            };
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放stmt
            if(pstmt != null){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //conn归还连接池
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}*/

	public static List<Posting> getPostings() throws SQLException{
		String sql = "SELECT * FROM blogdata ORDER BY id  " ;
		List<Posting> postings = new ArrayList<>();
		PreparedStatement pstmt = null;
		Connection conn =null;
		try{
			Properties pro = new Properties();
			pro.load(DBUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
			DataSource ds = DruidDataSourceFactory.createDataSource(pro);
			conn =ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				Posting posting = new Posting();
				posting.setId(rs.getInt("id"));
				posting.setTitle(rs.getString("title"));
				posting.setAuthor(rs.getString("author"));
				posting.setContent(rs.getString("content"));
				posting.setpicPath(rs.getString("picpath"));
				postings.add(posting);
			}
		}
		catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//conn归还连接池
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		return postings;
	}}

	public static List<Posting> getPostingslimited(int offset) throws SQLException{
		String sql = "SELECT * FROM blogdata ORDER BY id DESC LIMIT 6 OFFSET " + offset;
		List<Posting> postings = new ArrayList<>();
		PreparedStatement pstmt = null;
		Connection conn =null;
		try{
			Properties pro = new Properties();
			pro.load(DBUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
			DataSource ds = DruidDataSourceFactory.createDataSource(pro);
			conn =ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				Posting posting = new Posting();
				posting.setId(rs.getInt("id"));
				posting.setTitle(rs.getString("title"));
				posting.setAuthor(rs.getString("author"));
				posting.setContent(rs.getString("content"));
				posting.setpicPath(rs.getString("picpath"));
				postings.add(posting);
			}
		}
		catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//conn归还连接池
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return postings;
		}}

	public static List<Posting> getPostings(String key) throws SQLException {
		String sql = null;
		if (key!=null){
		 sql="SELECT * FROM `blogdata` WHERE" +
				" `title` LIKE '" + key + "'" +
				" OR `content` LIKE '" + key + "'" +
				" OR `author` = '" + key + "'";}
		else{
		 sql="SELECT * FROM `blogdata` " ;
		}
		List<Posting> postings = new ArrayList<>();
		PreparedStatement pstmt = null;
		Connection conn =null;
		try{
			Properties pro = new Properties();
			pro.load(DBUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
			DataSource ds = DruidDataSourceFactory.createDataSource(pro);
			conn =ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				Posting posting = new Posting();
				posting.setId(rs.getInt("id"));
				posting.setTitle(rs.getString("title"));
				posting.setAuthor(rs.getString("author"));
				posting.setContent(rs.getString("content"));
				posting.setpicPath(rs.getString("picpath"));
				postings.add(posting);
			}
		}
		catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//conn归还连接池
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return postings;
		}
//		try {
//			Class.forName(DB_DRIVER);
//		} catch (ClassNotFoundException e) {
//			e.printStackTrace();
//		}
//
//		List<Posting> postings = new ArrayList<>();
//
//		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
//			try (Statement statement = connection.createStatement()) {
//				ResultSet rs = statement.executeQuery(sql);
//				while (rs.next()) {
//					Posting posting = new Posting();
//					posting.setId(rs.getInt("id"));
//					posting.setTitle(rs.getString("title"));
//					posting.setAuthor(rs.getString("author"));
//					posting.setContent(rs.getString("content"));
//					posting.setpicPath(rs.getString("picpath"));
//					postings.add(posting);
//				}
//			}
//		}
//
//		return postings;
	}

	public static User getUser(String use) throws SQLException {
		String sql="SELECT * FROM `userdata` WHERE" +
				" `username` = " +"'"+ use +"'";
		User user = new User();
		PreparedStatement pstmt = null;
		Connection conn =null;
		try{
			Properties pro = new Properties();
			pro.load(DBUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
			DataSource ds = DruidDataSourceFactory.createDataSource(pro);
			conn =ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery(sql);
			rs.next();
			user.setName(rs.getString("username"));
			user.setPassword(rs.getString("password"));
		}
		catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//conn归还连接池
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
//		try {
//			Class.forName(DB_DRIVER);
//		} catch (ClassNotFoundException e) {
//			e.printStackTrace();
//		}
//
//		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
//			try (Statement statement = connection.createStatement()) {
//				ResultSet rs = statement.executeQuery(sql);
//					rs.next();
//					user.setName(rs.getString("username"));
//					user.setPassword(rs.getString("password"));
//			}
//		}
		return user;
	}}

	public static Posting getPosting(int id) throws SQLException {
		String sql = "SELECT * FROM `blogdata` WHERE" +
				" `id` = " + "'" + id + "'";
		Posting posting = new Posting();
		PreparedStatement pstmt = null;
		Connection conn =null;
		try{
			Properties pro = new Properties();
			pro.load(DBUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
			DataSource ds = DruidDataSourceFactory.createDataSource(pro);
			conn =ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery(sql);
			rs.next();
			posting.setTitle(rs.getString("title"));
			posting.setAuthor(rs.getString("author"));
			posting.setContent(rs.getString("content"));
			posting.setpicPath(rs.getString("picpath"));
		}
		catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//conn归还连接池
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
//		try {
//			Class.forName(DB_DRIVER);
//		} catch (ClassNotFoundException e) {
//			e.printStackTrace();
//		}
//
//		Posting posting = new Posting();
//
//		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
//			try (Statement statement = connection.createStatement()) {
//				ResultSet rs = statement.executeQuery(sql);
//					rs.next();
//					posting.setId(rs.getInt("id"));
//					posting.setTitle(rs.getString("title"));
//					posting.setAuthor(rs.getString("author"));
//					posting.setContent(rs.getString("content"));
//					posting.setpicPath(rs.getString("picpath"));
//				}
//			}
//		return posting;
//		}}
		return posting;
	}
	}}


